﻿-----CSDL Quan Ly Nha Sach-----------
If Exists(Select name From sysdatabases where name = 'QuanLyNhaSach')
	Drop database QuanLyNhaSach
Go	
create database QuanLyNhaSach
Go
--Chuyen vao Database vua tao de su dung
Use QuanLyNhaSach
Go

---------------------------------Tao Bang--------------------------------------
-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'NguoiDung')
        BEGIN
                PRINT 'Xoa bang NguoiDung'
                DROP  Table NguoiDung
        END
GO
PRINT 'Tao bang NguoiDung'
CREATE TABLE NguoiDung
(
	IdNguoiDung VARCHAR(30) PRIMARY KEY,
	PassND VARCHAR(30),
	HoTen NVARCHAR(30) NOT NULL,
	IdLoaiND int NOT NULL
)
Go
-------------------------------1.Phieu Nhap Sach---------------------------------
If Exists(Select name From sysobjects where name = 'PhieuNhapSach')
BEGIN
	 PRINT 'Xoa bang PhieuNhapSach'
	 Drop Table PhieuNhapSach 
END	 
Go	
PRINT 'Tao bang PhieuNhapSach'
Create Table PhieuNhapSach
(
	MaPhieuNhapSach int identity,
	NgayNhap datetime,
	TinhTrang int,
	Constraint pk_PhieuNhapSach Primary key(MaPhieuNhapSach)
)

-----------------------------2.Tac Gia---------------------------------

If Exists(Select name From sysobjects where name = 'TacGia')
BEGIN
	PRINT 'Xoa bang TacGia'
	Drop Table TacGia
END	
Go	
PRINT 'Tao bang TacGia'
Create Table TacGia
(
	MaTacGia int identity,
	TenTacGia nvarchar(100),
	TinhTrang int,
	Constraint pk_TacGia Primary key(MaTacGia)
)

Go
-----------------------------3.Loai Sach---------------------------------

If Exists(Select name From sysobjects where name = 'LoaiSach')
BEGIN
	PRINT 'Xoa bang LoaiSach'
	Drop Table LoaiSach
END	
Go	
PRINT 'Tao bang LoaiSach'
Create Table LoaiSach
(
	MaLoaiSach int identity,
	TenTheLoai nvarchar(100),
	TinhTrang int,
	Constraint pk_LoaiSach Primary key(MaLoaiSach)
)

Go
-----------------------------4.Sach---------------------------------
If Exists(Select name From sysobjects where name = 'Sach')
BEGIN
	PRINT 'Xoa bang Sach'
	Drop Table Sach
END
Go	
PRINT 'Tao bang Sach'
Create Table Sach
(
	MaSach int identity,
	TenSach nvarchar(100),
	MaLoaiSach int,
	MaTacGia int,
	SoLuongTon int,
	DonGiaNhap float,
	TinhTrang int,
	Constraint pk_Sach Primary key(MaSach)
)

Go
-----------------------------5.Chi Tiet Phieu Nhap Sach---------------------------------
If Exists(Select name From sysobjects where name = 'ChiTietPhieuNhapSach')
BEGIN
	PRINT 'Xoa bang ChiTietPhieuNhapSach'
	Drop Table ChiTietPhieuNhapSach
End	
Go	
PRINT 'Tao bang ChiTietPhieuNhapSach'
Create Table ChiTietPhieuNhapSach
(
	MaChiTietPhieuNhapSach int identity,
	MaPhieuNhapSach int,
	SoLuongNhap int,
	MaSach int,
	TinhTrang int,
	Constraint pk_ChiTietPhieuNhapSach Primary key(MaChiTietPhieuNhapSach)
)
Go

-----------------------------6.Hoa Don---------------------------------
If Exists(Select name From sysobjects where name = 'HoaDon')
BEGIN
	PRINT 'Xoa bang HoaDon'
	Drop Table HoaDon
END	
Go	
PRINT 'Tao bang HoaDon'
Create Table HoaDon
(
	MaHoaDon int identity,
	NgayLapHoaDon datetime,
	MaKhachHang int,
	TongTien int,
	TinhTrang int,	
	Constraint pk_HoaDon Primary key(MaHoaDon)
)
Go
-----------------------------7.Khach Hang---------------------------------
If Exists(Select name From sysobjects where name = 'KhachHang')
BEGIN
	PRINT 'Xoa Bang Khach Hang'
	Drop Table KhachHang
END	
Go	
PRINT 'Tao bang KhachHang'
Create Table KhachHang
(
	MaKhachHang int identity,
	TenKhachHang nvarchar(50),
	NoHienTai int,
	DiaChi nvarchar(100),
	DienThoai varchar(20),
	Email varchar(100),
	TinhTrang int,
	Constraint pk_KhachHang Primary key(MaKhachHang)
)
Go
-----------------------------8.Chi Tiet Hoa Don---------------------------------
If Exists(Select name From sysobjects where name = 'ChiTietHoaDon')
BEGIN
	PRINT 'Xoa bang ChiTietHoaDon'
	Drop Table ChiTietHoaDon
END	
Go	
PRINT 'Tao bang ChiTietHoaDon'
Create Table ChiTietHoaDon
(
	MaChiTietHoaDon int identity,
	MaHoaDon int,
	MaSach int,
	SoLuongBan int,
	DonGia float,
	TinhTrang int,
	Constraint pk_ChiTietHoaDon Primary key(MaChiTietHoaDon)
)
Go
-----------------------------9.Tham So---------------------------------
If Exists(Select name From sysobjects where name = 'ThamSo')
BEGIN
	PRINT 'Xoa bang ThamSo'
	Drop Table ThamSo
END
Go
PRINT 'Tao bang ThamSo'	
Create Table ThamSo
(
	MaThamSo int identity,
	LuongNhapToiThieu int,
	LuongTonToiThieu int,
	NoToiDa int,
	ThuKhongVuotQuaNo int, --1 or 0
	Constraint pk_ThamSo Primary key(MaThamSo)
)
Go

---------------------------------------10.Ton Kho--------------------------
If Exists(Select name From sysobjects where name = 'TonKho')
BEGIN
	PRINT 'Xoa bang TonKho'
	Drop Table TonKho
END
Go
PRINT 'Tao bang TonKho'	
Create Table TonKho
(
	MaTonKho int identity,
	ThangBaoCaoTonKho datetime,
	TinhTrang int,
	Constraint pk_TonKho Primary key(MaTonKho)
)
Go

---------------------------11.Chi Tiet Ton Kho-------------------
If Exists(Select name From sysobjects where name = 'ChiTietTonKho')
BEGIN
	PRINT 'Xoa bang ChiTietTonKho'
	Drop Table ChiTietTonKho
END
Go	
PRINT 'Tao bang ChiTietTonKho'
Create Table ChiTietTonKho
(
	MaChiTietTonKho int identity,
	MaTonKho int,
	MaSach int,
	TonDauKho int,
	TonCuoiKho int,
	PhatSinhKho int,
	TinhTrang int,
	Constraint pk_ChiTietTonKho Primary key(MaTonKho)
)
Go

---------------------12. Cong No ----------------------------
If Exists(Select name From sysobjects where name = 'CongNo')
BEGIN
	PRINT 'Xoa bang CongNo'
	Drop Table CongNo
END
Go	
PRINT 'Tao bang CongNo'
Create Table CongNo
(
	MaCongNo int identity,
	ThangBaoCaoCongNo datetime,
	TinhTrang int,
	Constraint pk_CongNo Primary key(MaCongNo)
)
Go

--------------------13. Chi Tiet Cong No--------------------------
If Exists(Select name From sysobjects where name = 'ChiTietCongNo')
BEGIN
	PRINT 'Xoa bang ChiTietCongNo'
	Drop Table ChiTietCongNo
END
Go	
PRINT 'Tao bang ChiTietCongNo'
Create Table ChiTietCongNo
(
	MaChiTietCongNo int identity,
	MaCongNo int,
	MaKhachHang int,
	NoDauCongNo int,
	PhatSinhCongNo int,
	NoCuoiCongNo int,
	TinhTrang int,
	Constraint pk_ChiTietCongNo Primary key(MaChiTietCongNo)
)
Go

-------------------14. Phieu Thu------------------------------
If Exists(Select name From sysobjects where name = 'PhieuThu')
BEGIN
	PRINT 'Xoa bang PhieuThu'
	Drop Table PhieuThu
END
Go	
PRINT 'Tao bang PhieuThu'
Create Table PhieuThu
(
	MaPhieuThu int identity,
	MaKhachHang int,
	NgayThuTien datetime,
	SoTienThu int,
	TinhTrang int
	Constraint pk_PhieuThu Primary key(MaPhieuThu)
)
Go
---------------------------------------Tao Khoa Ngoai--------------------------------
--------------------------------------------------------------------------------------
Alter Table Sach
Add Constraint fk_Sach_TacGia Foreign key(MaTacGia)
								References TacGia(MaTacGia)		
Go

Alter Table Sach
Add Constraint fk_Sach_LoaiSach Foreign key(MaLoaiSach)
								References LoaiSach(MaLoaiSach)		
Go

Alter Table ChiTietPhieuNhapSach
Add Constraint fk_ChiTietPhieuNhapSach_PhieuNhapSach Foreign key(MaPhieuNhapSach)
								References PhieuNhapSach(MaPhieuNhapSach)		
Go

Alter Table ChiTietPhieuNhapSach
Add Constraint fk_ChiTietPhieuNhapSach_Sach Foreign key(MaSach)
								References Sach(MaSach)		
Go

Alter Table ChiTietHoaDon
Add Constraint fk_ChiTietHoaDon_HoaDon Foreign key(MaHoaDon)
								References HoaDon(MaHoaDon)		
Go

Alter Table ChiTietHoaDon
Add Constraint fk_ChiTietHoaDon_Sach Foreign key(MaSach)
								References Sach(MaSach)		
Go

Alter Table HoaDon
Add Constraint fk_HoaDon_KhachHang Foreign key(MaKhachHang)
								References KhachHang(MaKhachHang)		
								
Alter Table ChiTietTonKho
Add Constraint fk_ChiTietTonKho_TonKho Foreign key(MaTonKho)
								References TonKho(MaTonKho)	
Go

Alter Table ChiTietTonKho
Add Constraint fk_ChiTietTonKho_Sach Foreign key(MaSach)
								References Sach(MaSach)	
Go

Alter Table PhieuThu
Add Constraint fk_PhieuThu_KhachHang Foreign key(MaKhachHang)
								References KhachHang(MaKhachHang)	
Go

Alter Table ChiTietCongNo
Add Constraint fk_ChiTietCongNo_KhachHang Foreign key(MaKhachHang)
								References KhachHang(MaKhachHang)	
Go

Alter Table ChiTietCongNo
Add Constraint fk_ChiTietCongNo_CongNo Foreign key(MaCongNo)
								References CongNo(MaCongNo)	
Go


-----------------------------------Them Du Lieu Cho Cac Bang---------------------------------
-----------------------------------Tabl.NguoiDung--------------------------------------------
---------------------------------------------------------------------------------------------
INSERT INTO NguoiDung(IdNguoiDung, PassND, HoTen, IdLoaiND) 
VALUES('12345','12345',N'Bùi Thanh Thoại','1')

INSERT INTO NguoiDung(IdNguoiDung, PassND, HoTen, IdLoaiND) 
VALUES('54321','54321',N'Trần Văn Tú','2')